﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Win32;

namespace TestSQLConnection
{
    class Program
    {
        private static string TestSQLConnection(string ConnectionString)
        {
            string Result = "";
            using (SqlConnection newSQLConection = new SqlConnection())
            {
                newSQLConection.ConnectionString = ConnectionString;
                try
                {
                    newSQLConection.Open();

                }
                catch (Exception Ex)
                {
                    Result = "Unable to connect to SQL Server. Error details:\n" + Ex.Message;
                    return Result;
                }
                newSQLConection.Close();
            }
            return Result;
        }
        private static bool Is64BitOS()
        {
            string strProcArchi = Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE");
            if (strProcArchi.IndexOf("64") > 0)
            {
                return true;
            }
            return false;
        }
        private static string CONFIG_DB_REG_KEY_LOC = "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Shared Tools\\Web Server Extensions\\{0}\\Secure\\ConfigDb";
        private static string CONFIG_DB_REG_KEY_VAL = "dsn";
        private static string Welcome = "********************************************\n" +
                                        "* Welcome to SP to SQL Connection test 1.0 *\n" +
                                        "*     Programmed by Ahmed Farag - 2011     *\n" +
                                        "********************************************\n";
        private static string Help = "Run the tool with no parameters to auto retrieve the SharePoint connection\n" +
                                     "string and test the connection to SQL Server.\n\n" +
                                     "Example:\n\n" +
                                     "C:\\> TestSQLConnection.exe\n\n" +
                                     "You also can test the connection to any SQL Server by supplying the SQLServer\n" +
                                     "and the DatabaseName parameters as follows:\n\n" +
                                     "\nTestSQLConnection [DATABASE_NAME]\\<SQL_INSTANCE> [DATABASE_NAME]\n\n" +
                                     "Example:\n\n" +
                                     "C:\\> TestSQLConnection.exe CONTOSOdbServer\\SPInstance SharePoint_Config\n\n" +
                                     "If the test is taking too long to return you can specify a timeout limit\n" +
                                     "in seconds as follows: (Keep trying for 15 seconds)\n\n" +
                                     "C:\\> TestSQLConnection.exe CONTOSOdbServer\\SPInstance SharePoint_Config 15\n\n" +
                                     "Please note: if you are test the SharePoint connection to SQL make sure you\n" +
                                     "are running the tool with a farm admin. The tool only supports x64 bit OS.";

        static void Main(string[] args)
        {
            //args = new string[1] { "ahmedfarag"};
            //args = new string[2] { "ahmedfarag","SharePoint_Config"};
            //args = new string[3] { "ahmedfarag", "SharePoint_Configg", "200" };

            //Show the welcome message
            Console.WriteLine(Welcome);
            string Result = "";
            
            //Check the entered arguments
            switch (args.Length)
            {
                case 0:
                    //Auto check SharePoint connection to SQL Server
                    Console.WriteLine("\nChecking SharePoint Version...\n");
                    //Test SharePoint 2007 Connection
                    Result = TestSharePointConnection("12.0");
                    if (Result == "SharePoint Not Found")
                        //Test SharePoint 2010 Connection
                        Result = TestSharePointConnection("14.0");
                    if (Result == "SharePoint Not Found")
                        //Test SharePoint 2013 Connection
                        Result = TestSharePointConnection("15.0");
                    if (Result == "SharePoint Not Found")
                        //Test SharePoint 2016 Connection
                        Result = TestSharePointConnection("16.0");
                    break;
                case 1:
                    //Display Help
                    Console.WriteLine("Tool Help:\n\n{0}", Help);
                    return;
                case 2:
                    //Test connection supplying sql server name and instance (if any) and the database name to connect to
                    string ConnectionString =
                    string.Format("Data Source={0};Initial Catalog={1};" +
                               "Integrated Security=True;Enlist=False;Connect Timeout=120", args[0], args[1]);
                    Result = TestSQLConnection(ConnectionString);
                    break;
                case 3:
                    //Test connection supplying sql server name and instance (if any) and the database name to connect to
                    // and also the timeout limit for the connection test
                    ConnectionString =
                    string.Format("Data Source={0};Initial Catalog={1};" +
                               "Integrated Security=True;Enlist=False;Connect Timeout=120", args[0], args[1]);
                    ConnectionString = ConnectionString.Replace("Timeout=120", "Timeout=" + args[2]);
                    Result = TestSQLConnection(ConnectionString);
                    break;
                default:
                    if (args.Length < 2)
                    {
                        Console.WriteLine("Invalid Arguments." +
                                          " Please supply the SQLServer and the DatabaseName" +
                                          " parameters as follows:" +
                                          "\nTestSQLConnection [DATABASE_NAME]\\<SQL_INSTANCE>" +
                                          " [DATABASE_NAME]");
                    }
                    break;
            }
            
            

            //Check the status of the connection
            if (Result == "")
            {
                Console.ForegroundColor = ConsoleColor.Green;
                Console.WriteLine("Connection Succeeded");
                Console.ResetColor();
            }
            else
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(Result);
                Console.ResetColor();
            }

        }
        static string GetSharePointConnectionString(string SharePointVersion)
        {
            return GetRegisteryKeyValue(string.Format(CONFIG_DB_REG_KEY_LOC, SharePointVersion), CONFIG_DB_REG_KEY_VAL);
        }
        static string GetRegisteryKeyValue(string RegisteryKey, string RegValue)
        {
            object ReturnedValue= Registry.GetValue(RegisteryKey, RegValue, "");
            string KeyValue = "";
            if(ReturnedValue != null)
            KeyValue = ReturnedValue.ToString();
            return KeyValue;
        }
        static string TestSharePointConnection(string Version)
        {
            string Result = "";
            try
            {
                //Retrive the SharePoint connection string from registery
                string ConnectionString = GetSharePointConnectionString(Version);
                if (ConnectionString == "")
                    Result = "SharePoint Not Found";
                else//Test SQL Connection with the retieved connection string
                {
                    string SharePointYear = "";
                    switch (Version)
                    {
                        case "12.0":
                            SharePointYear = "2007";
                            break;
                        case "14.0":
                            SharePointYear = "2010";
                            break;
                        case "15.0":
                            SharePointYear = "2013";
                            break;
                        case "16.0":
                            SharePointYear = "2016";
                            break;
                        default:
                            break;
                    }
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.WriteLine("SharePoint Version {0} Found.\n",SharePointYear);
                    Console.ForegroundColor = ConsoleColor.Yellow;
                    Console.WriteLine("Testing Connection to SQL Server. Please wait ...\n");
                    Console.ResetColor();
                    Result = TestSQLConnection(ConnectionString);
                }
            }
            catch (Exception Ex)
            {
                Result = "An error occured while testing SharePoint connection to SQL Server. Error details:\n" + Ex.Message;
            }
            return Result;
        }
    }
}
